package cn.aitrox.ry.util;

import cn.aitrox.ry.constant.CommonConstant;
import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    public static final String XLS = "xls";
    public static final String XLSX = "xlsx";

    public static List<List<String>> readFile(File file) throws IOException {
        Workbook workbook = getWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);
        List<List<String>> list = Lists.newArrayList();
        int headerCellCount = 0;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (null != row) {
                if (i == 0) {
                    headerCellCount = row.getLastCellNum();
                }
                List<String> rowData = new ArrayList<>();
                for (int j = 0; j < headerCellCount; j++) {
                    Cell cell = row.getCell(j);
                    if (null == cell) {
                        rowData.add(CommonConstant.EMPTY);
                    } else {
                        String colValue = getCellFormatValue(cell);
                        rowData.add(colValue);
                    }
                }
                list.add(rowData);
            }
        }

        return list;
    }

    private static Workbook getWorkbook(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().toLowerCase().endsWith(XLS)) {
            wb = new HSSFWorkbook(in);
        } else if (file.getName().toLowerCase().endsWith(XLSX)) {
            wb = new XSSFWorkbook(in);
        }

        return wb;
    }

    private static String getCellFormatValue(Cell cell) {
        String cellValue = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case NUMERIC:
                    double numericCellValue = cell.getNumericCellValue();
                    cellValue = handlerCellDoubleValue(numericCellValue);
                    break;
                case STRING:
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellValue = CommonConstant.EMPTY;
            }
        } else {
            cellValue = CommonConstant.EMPTY;
        }

        return cellValue;
    }

    private static String handlerCellDoubleValue(double val) {
        String str = String.valueOf(val);
        if (str.indexOf(CommonConstant.FILE_NAME_SEPARATOR) != -1) {
            String[] strArr = str.split(CommonConstant.FILE_NAME_SEPARATOR);
            if (strArr.length == 2 && strArr[1].equals(CommonConstant.ZERO)) {
                return strArr[0];
            }
        }

        return str;
    }

    public static void writeFile(List<List<String>> data, File file) throws IOException {

        FileOutputStream fs = null;
        try {
            File parentFile = file.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            fs = new FileOutputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook();
            writeSheet(workbook, data, null);
            workbook.write(fs);
        } finally {
            if (null != fs) {
                fs.close();
            }
        }
    }

    public static void writeFileMultiSheet(List<String> sheetNames, List<List<List<String>>> allSheetData, File file) throws IOException {
        FileOutputStream fs = null;
        try {
            File parentFile = file.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            fs = new FileOutputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook();
            for (int i = 0; i < sheetNames.size(); i++) {
                String sheetName = sheetNames.get(i);
                List<List<String>> sheetData = allSheetData.get(i);
                writeSheet(workbook, sheetData, sheetName);
            }
            workbook.write(fs);
        } finally {
            if (null != fs) {
                fs.close();
            }
        }
    }

    private static void writeSheet(XSSFWorkbook workbook, List<List<String>> sheetData, String sheetName) {
        XSSFSheet sheet;
        if (null == sheetName) {
            sheet = workbook.createSheet();
        } else {
            sheet = workbook.createSheet(sheetName);
        }
        if (null == sheetData || sheetData.size() == 0) {
            return;
        }
        int headerSize = sheetData.get(0).size();
        for (int i = 0; i < sheetData.size(); i++) {
            List<String> rowData = sheetData.get(i);
            if (null == rowData || rowData.size() == 0) {
                break;
            }
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < headerSize; j++) {
                XSSFCell cell = row.createCell(j);
                String cellText = null == rowData.get(j) ? "" : rowData.get(j);
                cell.setCellValue(cellText);
            }
        }
    }
}
